-- @owner: weixin_44096892
-- @date: 2025/6/29
-- @testpoint: 函数中使用

@conn PrimaryDbAmode;
--step0：前置条件;expect: 成功
drop extension if exists gms_lob;
drop extension if exists gms_output;
create extension gms_lob;
create extension gms_output;
select gms_output.enable(100000);

create or replace function cast_to_raw(strdata varchar2) returns raw
as 'select encode(cast($1 as bytea), ''hex'')::raw;'LANGUAGE SQL;/

drop table if exists tbl_testlob;
create table tbl_testlob(id int, c_lob clob, b_lob blob);
insert into tbl_testlob values(1, 'clob', cast_to_raw('blob'));
insert into tbl_testlob values(2, '中文clobobject测试', cast_to_raw('中文blobobject测试'));
drop function if exists func_clob();

--step1：--函数创建成功调用结果为
        --clob2:
        --clob1: clob test
        --clob2: clob test
        --clob3：中文clobobject测试
create or replace function func_clob() returns void
AS $$
DECLARE
    v_clob1 clob;
    v_clob2 clob;
    v_clob3 clob;
    len1 int;
    len3 int;
BEGIN
    select c_lob into v_clob1 from tbl_testlob where id = 1;
    gms_lob.open(v_clob1, gms_lob.LOB_READWRITE);
    gms_lob.append(v_clob1, ' test');
    len1 := gms_lob.getlength(v_clob1);
    --gms_output.put_line('clob2:' || v_clob2);
    raise info 'INFO:clob2: %',v_clob2;
    gms_lob.read(v_clob1, len1, 1, v_clob2);
    --gms_output.put_line('clob1:' || v_clob1);
    --gms_output.put_line('clob2:' || v_clob2);
    raise info 'INFO:clob1: %',v_clob1;
    raise info 'INFO:clob2: %',v_clob2;

    select c_lob into v_clob3 from tbl_testlob where id = 2;
    len3 := gms_lob.getlength(v_clob3);

    --gms_output.put_line('clob3:' || v_clob3);
    raise info 'INFO:clob3: %',v_clob3;

    gms_lob.close(v_clob1);
    gms_lob.freetemporary(v_clob2);
END;
$$LANGUAGE plpgsql;
/

--调用
select func_clob();

--step2：清理环境;expect: 成功
drop extension if exists gms_lob;
drop extension if exists gms_output;
drop table if exists tbl_testlob;
drop function if exists func_clob();
